﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;


namespace VeMayBayDAO
{
   public class DataProvider
    {
      public static SqlConnection con;
      public static SqlDataAdapter da;
      public static SqlCommand cm ;
      public static void Connect()
       {
           con = new SqlConnection();
           con.ConnectionString = VMBConfigs.DbConnectyionString;
           con.Open();
       }
      public static void DisConnect()
       {
           if (con.State == ConnectionState.Open|| con != null)
               con.Close();
       }
       //Insert Delete Update
     public  static int ExecNonQuery(string sql, List<SqlParameter> Sqlparams)
       {
           int num;
           try
           {
               Connect();
               cm = con.CreateCommand();
               cm.CommandType = CommandType.Text;
               cm.CommandText = sql;
               if (Sqlparams != null)
               {
                    foreach( SqlParameter para in Sqlparams )
                    {
                        cm.Parameters.Add(para);
                    }
               }
               num = cm.ExecuteNonQuery();
           }
           catch(Exception ex)
           {
               throw ex;
           }
           finally
           {
               DisConnect();
           }
           return num;
       }
      public static int ExecNonQuery(string sql)
       {
           return ExecNonQuery(sql, null);
       }
       //Max,min ,cout,Avg,.
      public static object ExecSalar(string sql,List<SqlParameter> SqlParams)
       {
           object data;
           try
           {
               Connect();
               cm = con.CreateCommand();
               cm.CommandType = CommandType.Text;
               cm.CommandText = sql;
               if (SqlParams != null)
               {
                   foreach (SqlParameter para in SqlParams)
                   {
                       cm.Parameters.Add(para);
                   }
               }
               data = cm.ExecuteScalar();
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               con.Close();
           }
           return data;
       }
     public  static object ExecSalar(string sql)
       {
           return ExecSalar(sql,null);
       }
     public  static SqlDataReader ExecReader(string sql,List<SqlParameter> SqlParams)
       {
         
           SqlDataReader da;
           try
           {
               Connect();
               cm = con.CreateCommand();
               cm.CommandType = CommandType.Text;
               cm.CommandText = sql;
               if (SqlParams != null)
               {
                   foreach (SqlParameter para in SqlParams)
                   {
                       cm.Parameters.Add(para);
                   }
               }
               da = cm.ExecuteReader();
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally { DisConnect(); }
           return da;
       }
     public  static SqlDataReader ExecReader(string sql)
       {
           return ExecReader(sql, null);
       }
      public static DataTable ExecQuery(string sql,List<SqlParameter > SqlParams)
       {
           DataTable dt = new DataTable();
           try
           {
               Connect();
               cm = con.CreateCommand();
               cm.CommandType = CommandType.Text;
               cm.CommandText = sql;
               if (SqlParams != null)
               {
                   foreach (SqlParameter para in SqlParams)
                   {
                       cm.Parameters.Add(para);
                   }
               }
               da = new SqlDataAdapter();
               da.SelectCommand = cm;
               da.Fill(dt);
           }
           catch (Exception ex)
           {
               
               throw ex;
               
           }
           finally { DisConnect(); }
           return dt;
       }

     public  static DataTable ExecQuery(string sql)
       {
           return ExecQuery(sql, null);
       }


     public static SqlCommand CreateCommand(string tenstore)
     {
         //chuoi ket noi
         //cnStr = ConfigurationManager.ConnectionStrings["cnString"].ConnectionString;
         //cn = new SqlConnection(cnStr);
         SqlCommand cmd = con.CreateCommand();
         try 
	    {
            Connect();
            
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = tenstore;
	    }
	    catch (Exception ex)
	    {
		
		    throw ex;
	    }
        
         finally{DisConnect();}
         return cmd;

     }
     public static SqlParameter CreateParamater(string name, string values, SqlDbType type)
     {
         

         SqlParameter prm = new SqlParameter();
         prm.ParameterName = name;
         prm.Value = values;
         prm.SqlDbType = type;
         return prm;
     }
     public static SqlCommand CreateCommand_query(string sql)
     {
         //
         
         SqlCommand cmd = con.CreateCommand();
         cmd.CommandText = sql;
         return cmd;

     }


    }
}

